﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
//using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Web.UI;
using System.Data.SQLite;

namespace BfLib.Data
{
    public class SQLiteBaseDAL
    {
        protected List<Pair> parameters;

        public SQLiteBaseDAL()
        {

        }

        public static SQLiteConnection GetConnection(string ConnectionStringName)
        {
            string cs = ConnectionStringName;// System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
            SQLiteConnection conn = new SQLiteConnection(cs);
            return conn;
        }

        protected static void OpenSqlConnection(SQLiteConnection connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        protected static DataTable GetDataTable(string ConnectionString, SQLiteCommand cmd)
        {
            DataTable data = new DataTable();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                cmd.Connection = connection;
                connection.Open();
                using (cmd)
                {
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(data);
                }
            }
            return data;
        }

        protected static DataTable GetDataTable(string ConnectionString, string SqlStr, List<Pair> parameters)
        {
            DataTable data = new DataTable();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(connection);
                cmd.Connection = connection;
                cmd.CommandText = SqlStr;
                
                foreach (Pair p in parameters)
                {
                    cmd.Parameters.AddWithValue((string)p.First, p.Second);
                }
                connection.Open();

                using (cmd)
                {
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(data);
                }
            }
            return data;
        }

        protected static DataSet GetDataSet(string ConnectionString, SQLiteCommand cmd)
        {
            DataSet data = new DataSet();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                cmd.Connection = connection;
                connection.Open();
                using (cmd)
                {
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(data);
                }
            }
            return data;
        }

        protected static DataSet GetDataSet(string ConnectionString, string SqlStr, List<Pair> parameters)
        {
            DataSet data = new DataSet();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(connection);
                cmd.Connection = connection;
                cmd.CommandText = SqlStr;
                foreach (Pair p in parameters)
                {
                    cmd.Parameters.AddWithValue((string)p.First, p.Second);
                }
                connection.Open();

                using (cmd)
                {
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(data);
                }
            }
            return data;
        }

        protected static int ExecuteNonQuery(string ConnectionString, SQLiteCommand cmd)
        {
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                cmd.Connection = connection;
                connection.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        protected static int ExecuteNonQuery(string ConnectionString, string SqlStr, List<Pair> parameters)
        {
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(connection);
                cmd.Connection = connection;
                cmd.CommandText = SqlStr;
                foreach (Pair p in parameters)
                {
                    cmd.Parameters.AddWithValue((string)p.First, p.Second);
                }
                connection.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        protected static object ExecuteScalar(string ConnectionString, string SqlStr, List<Pair> parameters)
        {
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(connection);
                cmd.Connection = connection;
                cmd.CommandText = SqlStr;
                foreach (Pair p in parameters)
                {
                    cmd.Parameters.AddWithValue((string)p.First, p.Second);
                }
                connection.Open();
                return cmd.ExecuteScalar();
            }
        }

        protected static object ExecuteScalar(string ConnectionString, SQLiteCommand cmd)
        {
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                cmd.Connection = connection;
                connection.Open();
                return cmd.ExecuteScalar();
            }
        }




















        //protected static int ExecuteNonQueryWithReturnValue(string spName, List<Pair> parameters)
        //{
        //    SQLiteCommand cmd = GetSPCommand(spName);

        //    foreach (Pair p in parameters)
        //    {
        //        cmd.Parameters.AddWithValue((string)p.First, p.Second);
        //    }
        //    cmd.Parameters.AddWithValue("@Return", "").Direction = ParameterDirection.ReturnValue;
        //    ExecuteNonQuery(cmd);
        //    return int.Parse(cmd.Parameters["@Return"].Value.ToString());
        //}

        //public static object GetValue(string SqlStr, List<Pair> Params, string FieldName)
        //{
        //    DataTable table = GetDataTable(SqlStr, Params);
        //    if (table == null)
        //    {
        //        return null;
        //    }
        //    else
        //    {
        //        if (table.Rows.Count > 0)
        //        {
        //            object result = null;
        //            if (FieldName != null)
        //            {
        //                if (table.Columns.Contains(FieldName))
        //                {
        //                    result = table.Rows[0][FieldName];
        //                }
        //                else
        //                {
        //                    throw new ArgumentException(String.Format("The DataTable does not contain a Column called {0}", FieldName));
        //                    //result = table.Rows[0][0];
        //                }
        //            }
        //            else
        //            {
        //                result = table.Rows[0][0];
        //            }
        //            return result;
        //        }
        //        else
        //        {
        //            return null;
        //        }
        //    }
        //}

        //public static object GetValue(string SqlStr, List<Pair> Params)
        //{
        //    return GetValue(SqlStr, Params, null);
        //}


        /*
            public class MyNewClass : ICreatable
            {
                public int Id { get; set; }
                public string Title { get; set; }
            
                //MyNewClass MUST implement ICreatable(SqlDataReader reader) 
     
                public void Create(SqlDataReader reader)
                {
                    Id = (int)reader[“Id”];
                    Title = (string)reader[“Title”];
                }
            }  
        */
        public static List<T> GetList<T>(string ConnectionString, SQLiteCommand command) where T : ISQLiteDataReaderCreatable, new()
        {
            List<T> results = new List<T>();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                command.Connection = connection;
                command.Connection.Open();
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T newObj = new T();
                    newObj.Create(reader);
                    results.Add(newObj);
                }
            }
            return results;
        }

        public static List<T> GetList<T>(string ConnectionString, string SqlStr, List<Pair> parameters) where T : ISQLiteDataReaderCreatable, new()
        {
            
            List<T> results = new List<T>();
            using (SQLiteConnection connection = GetConnection(ConnectionString))
            {
                SQLiteCommand command = new SQLiteCommand();
                command.CommandText = SqlStr;
                foreach (Pair p in parameters)
                {
                    command.Parameters.AddWithValue((string)p.First, p.Second);
                }

                command.Connection = connection;
                command.Connection.Open();

                SQLiteDataReader reader = command.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        T newObj = new T();
                        newObj.Create(reader);
                        results.Add(newObj);
                    }
                }
            }
            return results;
        }

        //public interface ICreatable
        //{
        //    void Create(SqlDataReader reader);
        //}
    }
}